<?php

namespace app\models;

use yii;
use yii\db\Query;

class Inquiry extends BaseModel
{
    public function __construct()
    {
        $this->table = yii::$app->db->tablePrefix . 'inquiry';
    }

    public function all($where = [], $offset = false, $limit = false, $order = false)
    {

    }

    public function one($where = [])
    {
        $db = new Query();
        $db->select('*')->from($this->table);
        if (!empty($where['id'])) {
            $db->andWhere(['id' => $where['id']]);
        }
        return $db->limit(1)->one();
    }

    public function insert_inquiry($data)
    {
        $keys = [
            'refer', 'name', 'consultation_time', 'reply_time', 'dispatch_time', 'ticket_id', 'ticket_detail',
            'ticket_url', 'country', 'email', 'recommend', 'inquiry_detail', 'star', 'service_name', 'link_time',
            'status'
        ];
        return yii::$app->db->createCommand()->batchInsert($this->table, $keys, $data)->execute();
    }

    public function get_inquiry_size($where)
    {
        $db = new Query();
        $db->select('count(id) as num')
            ->from($this->table);
        if ($where['refer']) $db->andWhere(['refer' => $where['refer']]);
        if ($where['star']) $db->andWhere(['star' => $where['star']]);
        if ($where['recommend']) $db->andWhere(['recommend' => $where['recommend']]);
        if ($where['status']) $db->andWhere(['status' => $where['status']]);
        if ($where['property']) $db->andWhere(['property' => $where['property']]);

        if (!empty($where['name'])) {
            $db->where(['like', 'name', $where['name']]);
        }

        if ($where['inquiry_detail']) {
            $db->andWhere(['like', 'inquiry_detail', $where['inquiry_detail']]);
        }

        if ($where['email']) $db->andWhere(['like', 'email', $where['email']]);


        if ($where['begin_time']) {
            $begin_time = strtotime($where['begin_time']) - 3600 * 8;
            $db->andWhere(['>=', 'consultation_time', $begin_time]);
        }


        if ($where['end_time']) {
            $end_time = strtotime($where['end_time']) + 3600 * 16;
            $db->andWhere(['<=', 'consultation_time', $end_time]);
        }

        if ($where['service_name']) {
            if (is_array($where['service_name'])) {
                $db->andWhere(['in', 'service_name', $where['service_name']]);
            } else {
                $db->andWhere(['service_name' => $where['service_name']]);
            }
        }

        $re = $db->one();
        return $re ? $re['num'] : 0;
    }

    public function get_inquiry_list($p, $s, $where = [])
    {
        $db = (new yii\db\Query)->select('a.*,b.name as sale_name')
            ->from($this->table . " as a")
            ->join('LEFT JOIN', yii::$app->db->tablePrefix . "staff as b", 'a.service_name=b.job_number');
        if ($where['refer']) {
            $db->andWhere(['refer' => $where['refer']]);
        }
        if (!empty($where['name'])) {
            $db->where(['like', 'a.name', $where['name']]);
        }

        if ($where['service_name']) {
            if (is_array($where['service_name'])) {
                $db->andWhere(['in', 'service_name', $where['service_name']]);
            } else {
                $db->andWhere(['service_name' => $where['service_name']]);
            }
        }

        if ($where['inquiry_detail']) {
            $db->andWhere(['like', 'inquiry_detail', $where['inquiry_detail']]);
        }

        if ($where['email']) {
            $db->andWhere(['like', 'a.email', $where['email']]);
        }

        if ($where['begin_time']) {
            $begin_time = strtotime($where['begin_time']) - 3600 * 8;
            $db->andWhere(['>=', 'consultation_time', $begin_time]);
        }


        if ($where['end_time']) {
            $end_time = strtotime($where['end_time']) + 3600 * 16;
            $db->andWhere(['<=', 'consultation_time', $end_time]);
        }


        if ($where['star']) {
            $db->andWhere(['star' => $where['star']]);
        }

        if ($where['recommend']) {
            $db->andWhere(['recommend' => $where['recommend']]);
        }

        if ($where['status']) {
            $db->andWhere(['a.status' => $where['status']]);
        }

        if ($where['property']) {
            $db->andWhere(['property' => $where['property']]);
        }

        $list = $db
            ->orderBy(['consultation_time' => SORT_DESC])
            ->offset(($p - 1) * $s)
            ->limit($s)
            ->all();
        return $list;
    }

    public function getInquiryList($where = [])
    {
        $db = (new yii\db\Query)->select('a.*,b.name as sale_name')
            ->from($this->table . " as a")
            ->join('LEFT JOIN', yii::$app->db->tablePrefix . "staff as b", 'a.service_name=b.job_number');
        if ($where['refer']) {
            $db->andWhere(['refer' => $where['refer']]);
        }
        if (!empty($where['name'])) {
            $db->where(['like', 'a.name', $where['name']]);
        }
        if ($where['inquiry_detail']) {
            $db->andWhere(['like', 'inquiry_detail', $where['inquiry_detail']]);
        }

        if ($where['email']) {
            $db->andWhere(['like', 'a.email', $where['email']]);
        }

        if ($where['begin_time']) {
            $begin_time = strtotime($where['begin_time']) - 3600 * 8;
            $db->andWhere(['>=', 'consultation_time', $begin_time]);
        }


        if ($where['end_time']) {
            $end_time = strtotime($where['end_time']) + 3600 * 16;
            $db->andWhere(['<=', 'consultation_time', $end_time]);
        }


        if ($where['star']) {
            $db->andWhere(['star' => $where['star']]);
        }

        if ($where['recommend']) {
            $db->andWhere(['recommend' => $where['recommend']]);
        }

        if ($where['service_name']) {
            $db->andWhere(['service_name' => $where['service_name']]);
        }
        if ($where['status']) {
            $db->andWhere(['a.status' => $where['status']]);
        }

        if ($where['property']) {
            $db->andWhere(['property' => $where['property']]);
        }

        $list = $db->orderBy(['consultation_time' => SORT_DESC])->all();
        return $list;
    }


    public function get_inquiry_list_by_status($p, $s, $status)
    {
        $db = new Query();
        $db->select('id,name,consultation_time,country,email,inquiry_detail,star')
            ->from(yii::$app->db->tablePrefix . "inquiry")
            ->where(['status' => $status]);
        //查询最近一个月未分配询盘
        $time = time() - 3600 * 24 * 30;
        return $db->andWhere(['>', 'consultation_time', $time])
            ->orderBy(['consultation_time' => SORT_DESC])
            ->offset(($p - 1) * $s)
            ->limit($s)
            ->all();
    }

    public function get_inquiry_size_by_status($status)
    {
        $db = new Query();
        $db->select('count(id) as num')
            ->from(yii::$app->db->tablePrefix . "inquiry")
            ->where(['status' => $status]);
        //查询最近一个月未分配询盘
        $time = time() - 3600 * 24 * 30;
        return $db->andWhere(['>', 'consultation_time', $time])
            ->orderBy(['consultation_time' => SORT_DESC])
            ->one();
    }

    public function get_inquiry_list_by_job_number($job_number)
    {
        $db = new Query();
        $db->select('*')
            ->from(yii::$app->db->tablePrefix . 'inquiry')
            ->where(['service_name' => $job_number])
            ->all();
    }

    public function get_inquiery_by_month($month = "")
    {
        $sql = "SELECT *, FROM_UNIXTIME( `consultation_time` ,  '%Y-%m-%d' ) as d,FROM_UNIXTIME( `dispatch_time` ,'%Y-%m-%d' ) as dispatch_time2,b.name as saleman_name
		FROM  `jingsourcing_inquiry` as a
		LEFT JOIN `jingsourcing_staff` as b
		ON a.service_name=b.job_number
		WHERE FROM_UNIXTIME( `consultation_time` ,  '%Y-%m' ) = " . ($month ? '\'' . $month . '\'' : "DATE_FORMAT( NOW( ) ,  '%Y-%m' )")
            . "ORDER BY consultation_time DESC";
        $list = yii::$app->db->createCommand($sql)->queryAll();
        $arr = [];
        foreach ($list as $k => $v) {
            $arr[$v['d']]['list'][] = $v;
            $arr[$v['d']]['num'] = 0;
            $arr[$v['d']]['status1'] = 0;//当天分配
            $arr[$v['d']]['status2'] = 0;//拒绝
            $arr[$v['d']]['saleman'] = [];
        }
        foreach ($arr as $k => $v) {
            foreach ($v['list'] as $k2 => $v2) {
                $arr[$k]['num']++;
                if ($v2['d'] == $v2['dispatch_time2']) {
                    $arr[$k]['status1']++;
                }
                if ($v2['status'] == 3) {
                    $arr[$k]['status2']++;
                }
                if ($v2['saleman_name']) {
                    if (isset($arr[$k]['saleman'][$v2['saleman_name']]['num'])) {
                        $arr[$k]['saleman'][$v2['saleman_name']]['num']++;
                    } else {
                        $arr[$k]['saleman'][$v2['saleman_name']]['num'] = 1;
                    }
                }
            }
        }
        return $arr;
        echo json_encode($arr);
        exit;
    }

    public function get_inquiry_by_month_service_name($year)
    {
        $sql = "SELECT a.*,b.name FROM
		(
		SELECT service_name, COUNT( id ) as num ,GROUP_CONCAT(property) AS order_status,FROM_UNIXTIME(  `consultation_time` ,  '%Y-%m' ) AS m
		FROM  `jingsourcing_inquiry` 
		WHERE service_name >0 AND inquiry_status=1
		AND FROM_UNIXTIME(  `consultation_time` ,  '%Y' ) = " . $year . "
		GROUP BY FROM_UNIXTIME(  `consultation_time` ,  '%Y-%m' ) , service_name
		ORDER BY service_name ASC , m DESC
		) AS a
		LEFT JOIN `jingsourcing_staff` AS b
		ON a.service_name=b.job_number
		";
        $list = yii::$app->db->createCommand($sql)->queryAll();
        $arr = [];
        foreach ($list as $k => $v) {
            $arr[$v['name']][$v['m']] = $v;
        }
        //echo json_encode($arr);exit;
        return $arr;
    }

    /**
     *统计年度询盘
     **/
    public function inquiryGroupByYear()
    {
        $db = new Query();
        $list = $db->select(["FROM_UNIXTIME(consultation_time,'%Y-%m') as month", "count(id) as num"])
            ->from($this->table)
            ->where(['inquiry_status' => 1])
            ->andWhere(['!=', 'status', 3])
            ->groupBy("month")
            ->all();

        $query = new Query();
        $list2 = $db->select(["FROM_UNIXTIME(consultation_time,'%Y-%m') as month", "count(id) as num"])
            ->from($this->table)
            ->where(['inquiry_status' => 1])
            ->groupBy("month")
            ->all();
        return ['list' => $list, 'list2' => $list2];
    }

    public function inquiryRecommendGroup($where = [])
    {
        $db = new Query();
        $db->select('count(id) as num,recommend')->from($this->table);
        if ($where['refer']) {
            $db->andWhere(['refer' => $where['refer']]);
        }
        if (!empty($where['name'])) {
            $db->where(['like', 'name', $where['name']]);
        }

        if ($where['inquiry_detail']) {
            $db->andWhere(['like', 'inquiry_detail', $where['inquiry_detail']]);
        }

        if ($where['email']) {
            $db->andWhere(['like', 'email', $where['email']]);
        }

        if ($where['begin_time']) {
            $begin_time = strtotime($where['begin_time']) - 3600 * 8;
            $db->andWhere(['>=', 'consultation_time', $begin_time]);
        }


        if ($where['end_time']) {
            $end_time = strtotime($where['end_time']) + 3600 * 16;
            $db->andWhere(['<=', 'consultation_time', $end_time]);
        }


        if ($where['star']) {
            $db->andWhere(['star' => $where['star']]);
        }

        if ($where['recommend']) {
            $db->andWhere(['recommend' => $where['recommend']]);
        }

        if ($where['service_name']) {
            $db->andWhere(['service_name' => $where['service_name']]);
        }

        if ($where['status']) {
            $db->andWhere(['status' => $where['status']]);
        }
        if ($where['property']) {
            $db->andWhere(['property' => $where['property']]);
        }
        return $db->groupBy('recommend')->all();
    }
}
